package org.jiangpeng.customer.helper;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.jiangpeng.customer.util.CollectionUtil;
import org.jiangpeng.customer.util.PropsUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 数据库操作辅助类
 *
 * @author jiangpeng
 */
public class DatabaseHelper {
    private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseHelper.class);

    private static final String DRIVER;
    private static final String URL;
    private static final String USERNAME;
    private static final String PWD;

    private static final ThreadLocal<Connection> CONNECTION_THREAD_LOCAL;

    private static final QueryRunner QUERY_RUNNER;

    static {
        CONNECTION_THREAD_LOCAL = new ThreadLocal<>();

        QUERY_RUNNER = new QueryRunner();

        Properties properties = PropsUtil.loadProps("scratch.properties");
        DRIVER = properties.getProperty("jdbc.driver");
        URL = properties.getProperty("jdbc.url");
        USERNAME = properties.getProperty("jdbc.username");
        PWD = properties.getProperty("jdbc.password");

        try {
            Class.forName(DRIVER);
        }
        catch (Exception e){
            LOGGER.error("con not load jdbc driver", e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection = CONNECTION_THREAD_LOCAL.get();
        if (connection == null){
            try {
                connection  = DriverManager.getConnection(URL, USERNAME,PWD);
            }
            catch (Exception e){
                LOGGER.error("get Connection failure", e);
            }
            finally {
                CONNECTION_THREAD_LOCAL.set(connection);
            }
        }
        return connection;
    }

    /**
     * 关闭数据库连接
     */
    public static void closeConnection(){
        Connection connection = CONNECTION_THREAD_LOCAL.get();
        if (connection != null){
            try {
                connection.close();;
            }
            catch (Exception e){
                LOGGER.error("close Connection failure", e);
            }
            finally {
                CONNECTION_THREAD_LOCAL.remove();
            }
        }
    }

    /**
     * 查询实体列表
     * @param tClass
     * @param sql
     * @param params
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> List<T> queryEntityList(Class<T> tClass, String sql, Object... params) throws SQLException{
        List<T> entityList;
        try {
            Connection connection = getConnection();
            entityList = QUERY_RUNNER.query(connection, sql, new BeanListHandler<T>(tClass), params);
        }
        catch (SQLException e){
            LOGGER.error("query entity list failure", e);
            throw e;
        }
        finally {
            closeConnection();
        }
        return entityList;
    }

    /**
     * 查询实体
     * @param tClass
     * @param sql
     * @param params
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> T queryEntity(Class<T> tClass, String sql, Object... params) throws SQLException{
        T entity;
        try {
            Connection connection = getConnection();
            entity = QUERY_RUNNER.query(connection, sql, new BeanHandler<T>(tClass), params);
        }
        catch (SQLException e){
            LOGGER.error("query entity failure", e);
            throw e;
        }
        return entity;
    }

    /**
     * 执行查询语句
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> executeQuery(String sql, Object... params) throws SQLException{
        List<Map<String, Object>> result;
        try {
            Connection connection = getConnection();
            result = QUERY_RUNNER.query(connection, sql, new MapListHandler(), params);
        }
        catch (SQLException e){
            LOGGER.error("execute query failure", e);
            throw e;
        }
        return result;
    }

    /**
     * 执行跟新操作（包括update，insert, delete）
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(String sql, Object... params) throws SQLException{
        int rows = 0;
        try {
            Connection connection = getConnection();
            rows = QUERY_RUNNER.update(connection, sql, params);
        }
        catch (SQLException e){
            LOGGER.error("execute query failure", e);
            throw e;
        }
        return rows;
    }

    /**
     * 插入实体
     * @param tClass
     * @param fieldMap
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> boolean insertEntity(Class<T> tClass, Map<String, Object> fieldMap) throws SQLException{
        if(fieldMap.isEmpty()){
            LOGGER.error("can not insert entity: fieldMap is empty");
            return false;
        }
        StringBuffer sql = new StringBuffer("INSERT INTO ");
        sql.append(getTableName(tClass));
        StringBuffer columns = new StringBuffer("(");
        StringBuffer values = new StringBuffer("(");
        for (String filedName : fieldMap.keySet()){
            columns.append(filedName).append(", ");
            values.append("?, ");
        }
        columns.replace(columns.lastIndexOf(", "), columns.length(), ")");
        values.replace(values.lastIndexOf(", "), values.length(), ")");
        sql.append(columns.toString()).append(" values ").append(values.toString());
        Object[] params = fieldMap.values().toArray();
        return executeUpdate(sql.toString(), params) == 1;
    }

    /**
     *  修改实体
     * @param tClass
     * @param id
     * @param fieldMap
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> boolean updateEntity(Class<T> tClass, long id, Map<String, Object> fieldMap) throws SQLException{
        if (fieldMap.isEmpty()){
            LOGGER.error("can not update entity: fieldMap is empty");
            return false;
        }
        StringBuffer sql = new StringBuffer("UPDATE ");
        sql.append(getTableName(tClass));
        sql.append(" SET ");
        StringBuffer columns = new StringBuffer();
        for (String fieldName : fieldMap.keySet()){
            columns.append(fieldName).append("=?, ");
        }
        sql.append(columns.substring(0, columns.lastIndexOf(", "))).append(" WHERE ID = ?");
        List<Object> paramList = new ArrayList<>();
        paramList.addAll(fieldMap.values());
        paramList.add(id);
        Object[] params = paramList.toArray();

        return executeUpdate(sql.toString(), params) == 1;
    }

    /**
     * 删除实体
     * @param tClass
     * @param id
     * @param <T>
     * @return
     * @throws SQLException
     */
    public static <T> boolean deleteEntity(Class<T> tClass, long id) throws SQLException{
        StringBuffer sql = new StringBuffer("DELETE FROM ");
        sql.append(getTableName(tClass));
        sql.append(" WHERE id = ?");
        return executeUpdate(sql.toString(), id) == 1;
    }

    private static String getTableName(Class<?> tClass){
        return tClass.getSimpleName();
    }
}
